package com.babel.util.util;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

/**
 * 从excel导入数据
 */
public class ExcelImportUtil {
	
//	public static void main(String[] args) throws Exception {
//		FileInputStream inputStream = new FileInputStream(new File("src/main/java/777.xlsx"));
//		String[] coloums= {"type","content","answer","itemA","itemB","itemC","itemD","itemE","itemF","itemG","itemH"};
//		List<Question> dateformExcel = ExcelImportUtil.getSpecilaList(inputStream,new Question(),coloums,2);
//		for (Question person : dateformExcel) {
//			System.out.println(person);
//		}
//	}

	/**
	 * 默认从第一行开始导入excel数据
	 * @param inputStream excel文件流
	 * @param t 包装类的类型
	 * @param coloums excel每个字段对应的属性（顺序一致）
	 * @return List<T>
	 * @throws Exception
	 */
	public static <T> List<T> getSpecilaList(InputStream inputStream,T t,String[] coloums) throws Exception{
		return getSpecilaList(inputStream,t,coloums,1,0,0);
	}
	
	/**
	 * 从指定行开始导入excel数据 默认取每行的所有列数据
	 * @param inputStream excel文件流
	 * @param t 包装类的类型
	 * @param coloums excel每个字段对应的属性（顺序一致）
	 * @param startRowNum 从Excel文件第几行开始获取数据
	 * @return List<T>
	 * @throws Exception
	 */
	public static <T> List<T> getSpecilaList(InputStream inputStream,T t,String[] coloums,int startRowNum) throws Exception{
		return getSpecilaList(inputStream,t,coloums,startRowNum,0,0);
	}
	
	/**
	 * 从指定行开始导入excel数据  取每行的指定列的数据
	 * @param inputStream excel文件流
	 * @param t 包装类的类型
	 * @param coloums excel每个字段对应的属性（顺序一致）
	 * @param startRowNum 从Excel文件第几行开始获取数据
	 * @param startCol 起始列 （从0开始）
	 * @param endCol 结束列
	 * @return List<T>
	 * @throws Exception
	 */
	public static <T> List<T> getSpecilaList(InputStream inputStream,T t,String[] coloums,int startRowNum,int startCol,int endCol) throws Exception {
		Class<? extends Object> clazz = t.getClass();
		List<T> resultlist = new ArrayList<>();
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		XSSFSheet sheet = workbook.getSheetAt(0);
		//遍历Excel所有行
		for (int i = startRowNum-1; i < sheet.getPhysicalNumberOfRows(); i++) {
			XSSFRow row = sheet.getRow(i);
			if (row != null) {
				int end = (endCol==0?row.getLastCellNum():endCol);
				boolean flag = false;
				T obj = (T) clazz.newInstance();
				//遍历每行的所有列
				int c = 0;
				for (int j=startCol;j<end;j++) {
					if(row.getCell(j)!=null) {
						row.getCell(j).setCellType(CellType.STRING);
						//如果该单元格有数据，则给实例对象赋值
						if (row.getCell(j).getStringCellValue() != null && !"".equals(row.getCell(j).getStringCellValue())) {
							for (Field field : obj.getClass().getDeclaredFields()) {
								if (field.getName().equals(coloums[c])) {
									if (!"".equals(row.getCell(j).getStringCellValue()) && row.getCell(j).getStringCellValue() != null)
										flag = true;
									field = clazz.getDeclaredField(coloums[c]);
									field.setAccessible(true);
									setProperty(row, obj, j, field);
									break;
								}
							}
						}
					}
					c++;
				}
				if(flag)
					resultlist.add(obj);
			}
		}
		return resultlist;
	}
	
	private static <T> void setProperty(XSSFRow row, T obj, int j, Field field) throws IllegalAccessException {
		switch (field.getType().getName()) {
		case "java.lang.String":
			field.set(obj, row.getCell(j).getStringCellValue());
			break;
		case "java.lang.Integer":
			field.set(obj, Integer.parseInt(row.getCell(j).getStringCellValue()));
			break;									
		case "java.lang.Double":
			field.set(obj, Double.parseDouble(row.getCell(j).getStringCellValue()));
			break;									
		case "java.lang.Long":
			field.set(obj, Long.parseLong(row.getCell(j).getStringCellValue()));
			break;									
		}
	}
	
	/**
	 * 专门导出每行一列数据的Excel
	 * @param in 文件流
	 * @param startRowNum 从Excel文件第几行开始获取数据
	 * @return List<String>
	 * @throws Exception
	 */
	public static List<String> getList(InputStream in,int startRowNum) throws Exception{
		List<String> list = new ArrayList<String>();
		XSSFWorkbook workbook = new XSSFWorkbook(in);
		XSSFSheet sheet = workbook.getSheetAt(0);
		int length = sheet.getPhysicalNumberOfRows();
		for (int i = startRowNum-1; i < length; i++) {
			XSSFRow row = sheet.getRow(i);
			if (row != null) {
				for (Cell cell : row) {
					cell.setCellType(CellType.STRING);
					if(cell.getStringCellValue()!=null && !"".equals(cell.getStringCellValue())){
						list.add(cell.getStringCellValue());
					}
				}
			}
		}
		return list;
	}
	
}
//
//class Question{
//	private String type;
//	private String content;
//	private String answer;
//	private String itemA;
//	private String itemB;
//	private String itemC;
//	private String itemD;
//	private String itemE;
//	private String itemF;
//	private String itemG;
//	private String itemH;
//	public String getType() {
//		return type;
//	}
//	public void setType(String type) {
//		this.type = type;
//	}
//	public String getContent() {
//		return content;
//	}
//	public void setContent(String content) {
//		this.content = content;
//	}
//	public String getAnswer() {
//		return answer;
//	}
//	public void setAnswer(String answer) {
//		this.answer = answer;
//	}
//	public String getItemA() {
//		return itemA;
//	}
//	public void setItemA(String itemA) {
//		this.itemA = itemA;
//	}
//	public String getItemB() {
//		return itemB;
//	}
//	public void setItemB(String itemB) {
//		this.itemB = itemB;
//	}
//	public String getItemC() {
//		return itemC;
//	}
//	public void setItemC(String itemC) {
//		this.itemC = itemC;
//	}
//	public String getItemD() {
//		return itemD;
//	}
//	public void setItemD(String itemD) {
//		this.itemD = itemD;
//	}
//	public String getItemE() {
//		return itemE;
//	}
//	public void setItemE(String itemE) {
//		this.itemE = itemE;
//	}
//	public String getItemF() {
//		return itemF;
//	}
//	public void setItemF(String itemF) {
//		this.itemF = itemF;
//	}
//	public String getItemG() {
//		return itemG;
//	}
//	public void setItemG(String itemG) {
//		this.itemG = itemG;
//	}
//	public String getItemH() {
//		return itemH;
//	}
//	public void setItemH(String itemH) {
//		this.itemH = itemH;
//	}
//	@Override
//	public String toString() {
//		return "Question [type=" + type + ", content=" + content + ", answer=" + answer + ", itemA=" + itemA
//				+ ", itemB=" + itemB + ", itemC=" + itemC + ", itemD=" + itemD + ", itemE=" + itemE + ", itemF=" + itemF
//				+ ", itemG=" + itemG + ", itemH=" + itemH + "]";
//	}
//	
//}
